package com.foreveross.fetl.core.domain.task.repository.impl;

import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.foreveross.fetl.common.jpa.EntityHibernateRepository;
import com.foreveross.fetl.core.domain.task.FlightInfoEntity;
import com.foreveross.fetl.core.domain.task.repository.FlightInfoEntityRepository;
import com.foreveross.fetl.util.DatabaseBeanUtils;


@Repository("flightInfoEntityRepository")
public class FlightInfoEntityRepositoryImpl extends EntityHibernateRepository
		implements FlightInfoEntityRepository {

	@Transactional
	public List<FlightInfoEntity> findByLine(String fromCityCode,
			String toCityCode) {
		//return this.find(QuerySettings.create(FlightInfoEntity.class)
		//		.eq("fromCity", fromCityCode).eq("toCity", toCityCode));
		String sql = "SELECT\n" + "t.ID AS id,\n" + "t.FROMCITY AS fromCity,\n"
				+ "t.FROMCITYNAME AS fromCityName,\n" + "t.TOCITY AS toCity,\n"
				+ "t.TOCITYNAME AS toCityName,\n" + "t.FLIGHTNO AS flightNO,\n"
				+ "t.AREA_CODE AS areaCode,\n" + "t.AREA_NAME AS areaName,\n"
				+ "t.COMPANY_CODE AS companyCode,\n"
				+ "t.COMPANY_NAME AS companyName,\n"
				+ "t.FLIGHT_START_TIME AS startTime,\n"
				+ "t.FLIGHT_END_TIME AS endTime,\n"
				+ "t.GRAB_CHANNEL_NUM AS grabChannel\n" + "FROM\n"
				+ "fdc_flightinfo t\n" + "WHERE t.FROMCITY = ?\n"
				+ "AND t.TOCITY = ?";
		return DatabaseBeanUtils.getBeanList(sql, FlightInfoEntity.class,
				new Object[] { fromCityCode, toCityCode });
	}

	public List<FlightInfoEntity> findFlightInfoEntitys(String companyCode,
			String fromCityCode, String toCityCode) {
		//return this.find(QuerySettings.create(FlightInfoEntity.class).eq("companyCode", companyCode)
		//		.eq("fromCity", fromCityCode).eq("toCity", toCityCode));
		String sql = "SELECT\n" + "t.ID AS id,\n" + "t.FROMCITY AS fromCity,\n"
				+ "t.FROMCITYNAME AS fromCityName,\n" + "t.TOCITY AS toCity,\n"
				+ "t.TOCITYNAME AS toCityName,\n" + "t.FLIGHTNO AS flightNO,\n"
				+ "t.AREA_CODE AS areaCode,\n" + "t.AREA_NAME AS areaName,\n"
				+ "t.COMPANY_CODE AS companyCode,\n"
				+ "t.COMPANY_NAME AS companyName,\n"
				+ "t.FLIGHT_START_TIME AS startTime,\n"
				+ "t.FLIGHT_END_TIME AS endTime,\n"
				+ "t.GRAB_CHANNEL_NUM AS grabChannel\n" + "FROM\n"
				+ "fdc_flightinfo  t\n" + "WHERE t.COMPANY_CODE =?\n"
				+ "AND t.FROMCITY = ?\n" + "AND t.TOCITY = ?";
		return DatabaseBeanUtils.getBeanList(sql, FlightInfoEntity.class,
				new Object[] { companyCode, fromCityCode, toCityCode });
	}
	public List<FlightInfoEntity> findFlightInfoEntitys(String companyCode) {
		String sql = "SELECT\n" + "t.ID AS id,\n" + "t.FROMCITY AS fromCity,\n"
				+ "t.FROMCITYNAME AS fromCityName,\n" + "t.TOCITY AS toCity,\n"
				+ "t.TOCITYNAME AS toCityName,\n" + "t.FLIGHTNO AS flightNO,\n"
				+ "t.AREA_CODE AS areaCode,\n" + "t.AREA_NAME AS areaName,\n"
				+ "t.COMPANY_CODE AS companyCode,\n"
				+ "t.COMPANY_NAME AS companyName,\n"
				+ "t.FLIGHT_START_TIME AS startTime,\n"
				+ "t.FLIGHT_END_TIME AS endTime,\n"
				+ "t.GRAB_CHANNEL_NUM AS grabChannel\n" + "FROM\n"
				+ "fdc_flightinfo  t\n" + "WHERE t.COMPANY_CODE =? order by t.FLIGHTNO";
		return DatabaseBeanUtils.getBeanList(sql, FlightInfoEntity.class,
				new Object[] { companyCode});
	}
	
	public List<FlightInfoEntity> findDistinctFlightInfoEntitys(String companyCode,
			String fromCityCode, String toCityCode) {
		//return this.find(QuerySettings.create(FlightInfoEntity.class).eq("companyCode", companyCode)
		//		.eq("fromCity", fromCityCode).eq("toCity", toCityCode).asc("flightNo"));
		String sql = 
				"SELECT\n" +
						"distinct\n" + 
						"t.FROMCITY AS fromCity,\n" + 
						"t.FROMCITYNAME AS fromCityName,\n" + 
						"t.TOCITY AS toCity,\n" + 
						"t.TOCITYNAME AS toCityName,\n" + 
						"t.FLIGHTNO AS flightNO,\n" + 
						"t.COMPANY_CODE AS companyCode,\n" + 
						"t.COMPANY_NAME AS companyName,\n" + 
						"Substr(t.FLIGHT_START_TIME , 0, 2) AS startTime\n" + 
						"FROM fdc_flightinfo  t\n" + 
						"WHERE t.COMPANY_CODE = ? \n" + 
						"AND t.FROMCITY = ? \n" + 
						"AND t.TOCITY = ? \n" + 
						"ORDER BY T.FLIGHTNO";
		return DatabaseBeanUtils.getBeanList(sql, FlightInfoEntity.class,
				new Object[] { companyCode, fromCityCode, toCityCode });
	}
}
